# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
# 加载 settings 文件
from scrapy.utils.project import get_project_settings
import pymysql

class ScrapyReadbook06Pipeline:

    def open_spider(self, spider):
        self.fp = open('book.txt', 'w', encoding='utf-8')

    def process_item(self, item, spider):
        self.fp.write(str(item) + '\n')
        return item

    def close_spider(self, spider):
        self.fp.close()

class MysqlPipeline:

    __conn = None
    __cursor = None

    def open_spider(self, spider):
        settings = get_project_settings()
        # DB_HOST = "127.0.0.1"
        # DB_PORT = 3306
        # DB_USER = "root"
        # DB_PASS = "root"
        # DB_NAME = "silence"
        # DB_CHARSET = "utf8"
        db_host = settings.get('DB_HOST')
        db_port = settings.get('DB_PORT')
        db_user = settings.get('DB_USER')
        db_pass = settings.get('DB_PASS')
        db_name = settings.get('DB_NAME')
        db_charset = settings.get('DB_CHARSET')
        self.__conn = pymysql.connect(
            host=db_host,
            port=db_port,
            user=db_user,
            password=db_pass,
            database=db_name,
            charset=db_charset
        )
        self.__cursor = self.__conn.cursor()


    def process_item(self, item, spider):
        sql = f'insert into book(name, src) values("{item.get('name')}", "{item.get('src')}")'
        self.__cursor.execute(sql)
        self.__conn.commit()
        return item

    def close_spider(self, spider):
        self.__cursor.close()
        self.__conn.close()
